package com.dao.impl;

import com.bean.Owner;
import com.dao.OwnerDAO;
import com.util.C3p0Utils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class OwnerDAOImpl implements OwnerDAO {

    /**
     * BeanListHandler将结果列表封装
     *
     * @return
     * @throws SQLException
     */
    @Override
    public List<Owner> selectAll() throws SQLException {

        return C3p0Utils.qr.query("select * from ownermsg", new BeanListHandler<Owner>(Owner.class));
    }

    /**
     * BeanHandler将单个列表封装
     *
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public List<Owner> selectById(String id) throws SQLException {
        return C3p0Utils.qr.query("select * from ownermsg  where ownerId =?", new BeanListHandler<>(Owner.class), id);
    }
    @Override
    public boolean deleteById(Integer id) throws SQLException {
        return C3p0Utils.qr.update("delete from ownermsg where Id=?", id) > 0;
    }
    @Override
    public boolean deleteAll(String[] idsArr) throws SQLException {
        StringBuffer sql = new StringBuffer("delete from ownermsg where Id in (");
        for (int i = 0; i < idsArr.length; i++) {
            sql.append("?,");
        }
        sql.deleteCharAt(sql.length() - 1);
        sql.append(")");
        return C3p0Utils.qr.update(sql.toString(), idsArr) > 0;
    }
    @Override
    public boolean insert(Owner o) throws SQLException {
        return C3p0Utils.qr.update("insert into ownermsg values(null,?,?,?,?,?,?,?,?,?,?,?)", o.getOwnerId(), o.getOwnerName(), o.getOwnerPassword(),
                o.getOwnerSex(), o.getOwnerPhone(), o.getOwnerIdentity(), o.getOwnerPic(), o.getHaveTime(), o.getBuildId(), o.getUnitId(), o.getRoomNumber()) > 0;
    }
    @Override
    public boolean update(Owner o) throws SQLException {
        return C3p0Utils.qr.update("update ownermsg set ownerId=?,ownerName=?,ownerPassword=?,ownerSex=?,ownerPhone=?,ownerIdentity=?,ownerPic=?," +
                        "haveTime=?,buildId=?,unitId=?,roomNumber=? where Id=?;", o.getOwnerId(), o.getOwnerName(), o.getOwnerPassword(), o.getOwnerSex(),
                o.getOwnerPhone(), o.getOwnerIdentity(), o.getOwnerPic(), o.getHaveTime(), o.getBuildId(), o.getUnitId(), o.getRoomNumber(),o.getId()) > 0;
    }

    /**
     * ScalarHandler用来将单个Object类型封装
     *
     * @return
     * @throws SQLException
     */
    @Override
    public Integer count() throws SQLException {
        int n = C3p0Utils.qr.query("select count(*) from ownermsg", new ScalarHandler<Long>()).intValue();
        return n;
    }
    @Override
    public Integer countSearch(String search) throws SQLException {
        int n = C3p0Utils.qr.query("select count(*) from ownermsg where concat(ownerId,ownerName,ownerPassword,ownerSex,ownerPhone,ownerIdentity," +
                "ownerPic,haveTime,buildId,unitId,roomNumber) like ?", new ScalarHandler<Long>(), "%" + search + "%").intValue();
        return n;
    }
    @Override
    public List<Owner> selectAllPagination(Integer offset, Integer limit) throws SQLException {
        return C3p0Utils.qr.query("select * from ownermsg limit ?,?", new BeanListHandler<>(Owner.class), offset, limit);

    }
    @Override
    public List<Owner> selectAllPaginationSearch(Integer offset, Integer limit, String search) throws SQLException {
        return C3p0Utils.qr.query("select * from ownermsg where concat(ownerId,ownerName,ownerPassword,ownerSex,ownerPhone,ownerIdentity,ownerPic," +
                "haveTime,buildId,unitId,roomNumber) like ? limit ?,?", new BeanListHandler<>(Owner.class), "%"+search+"%", offset, limit);

    }

}
